![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
In the previous chapter, we learned how to process query results with JDBC. In this chapter, well take these query results and put them to use in a multimedia application. The application well be developing, IconStore, will connect to a database, query for image data stored in database tables, and display the images on a canvas. Its all very simple, and it puts the JDBC to good use by building a dynamic application totally driven by data stored in tables.
The IconStore application will utilize two database tables: ICONCATEGORY and ICONSTORE. The ICONCATEGORY table contains information about image categories, which can be items like printers, sports, and tools. The ICONSTORE table contains information about each image. Tables 8.1 and 8.2 show the database tables underlying data structures.
Note that the CATEGORY column in the ICONSTORE is a foreign key into the ICONCATEGORY table. If the category ID for sports is 1, you can obtain a result set containing all of the sports images by using this statement:
SELECT ID, DESCRIPTION, ICON FROM ICONSTORE WHERE CATEGORY = 1
Column Name | SQL Type | Description |
---|---|---|
CATEGORY | INTEGER | Category ID |
DESCRIPTION | VARCHAR | Description of the image category |
Column Name | SQL Type | Description |
---|---|---|
ID | INTEGER | Image ID |
DESCRIPTION | VARCHAR | Description of the image |
CATEGORY | INTEGER | Category ID |
ICON | VARBINARY | Binary image |
Now, lets take a look at whats going on in the application:
As you can see, IconStore will not be too complicated, but it will serve as a very good foundation for developing database-driven applications.
Now that weve established the applications requirements, we need to build the underlying database. Well look at a simple JDBC application to accomplish this, although it may be created by any number of methods. Listing 8.1 shows the BuildDB.java source code. This application uses the SimpleText JDBC driver (covered in great detail in Chapter 10) to create the ICONCATEGORY and ICONSTORE tables, but any JDBC driver can be used in its place.
Listing 8.1 Building the IconStore database.
import java.sql.*; import java.io.*; class BuildDB { // // main // public static void main(String args[]) { try { // Create an instance of the driver java.sql.Driver d = (java.sql.Driver) Class.forName ( "jdbc.SimpleText.SimpleTextDriver").newInstance(); // Properties for the driver java.util.Properties prop = new java.util.Properties(); // URL to use to connect String url = "jdbc:SimpleText"; // The only property supported by the SimpleText driver // is "Directory." prop.put("Directory", "/java/IconStore"); // Connect to the SimpleText driver Connection con = DriverManager.getConnection(url, prop); // Create the category table buildCategory(con, "IconCategory"); // Create the IconStore table buildIconStore(con, "IconStore"); // Close the connection con.close(); } catch (SQLException ex) { System.out.println("\n*** SQLException caught ***\n"); while (ex != null) { System.out.println("SQLState: " + ex.getSQLState()); System.out.println("Message: " + ex.getMessage()); System.out.println("Vendor: " + ex.getErrorCode()); ex = ex.getNextException (); } System.out.println(""); } catch (java.lang.Exception ex) { ex.printStackTrace (); } } // // BuildCategory // Given a connection object and a table name, create the IconStore // category database table. // protected static void buildCategory( Connection con, String table) throws SQLException { System.out.println("Creating " + table); Statement stmt = con.createStatement(); // Create the SQL statement String sql = "create table " + table + " (CATEGORY NUMBER, DESCRIPTION VARCHAR)"; // Create the table stmt.executeUpdate(sql); // Create some data using the statement stmt.executeUpdate("INSERT INTO " + table + " VALUES (1, 'Printers')"); stmt.executeUpdate("INSERT INTO " + table + " VALUES (2, 'Sports')"); stmt.executeUpdate("INSERT INTO " + table + " VALUES (3, 'Tools')"); } // // BuildIconStore // Given a connection object and a table name, create the IconStore // icon database table. // protected static void buildIconStore( Connection con, String table) throws SQLException { System.out.println("Creating " + table); Statement stmt = con.createStatement(); // Create the SQL statement String sql = "create table " + table + " (ID NUMBER, DESCRIPTION VARCHAR, CATEGORY NUMBER, ICON BINARY)"; // Create the table stmt.executeUpdate(sql); stmt.close(); // Create some data using a prepared statement sql = "insert into " + table + " values(?,?,?,?)"; FileInputStream file; PreparedStatement ps = con.prepareStatement(sql); int category; int id = 1; // Add the printer icons category = 1; addIconRecord(ps, id++, "Printer 1", category, "printers/print.gif"); addIconRecord(ps, id++, "Printer 2", category, "printers/print0.gif"); // Add the sports icons category = 2; addIconRecord(ps, id++, "Archery", category, "sports/ sport_archery.gif"); addIconRecord(ps, id++, "Baseball", category, "sports/ sport_baseball.gif"); // Add the tools category = 3; addIconRecord(ps, id++, "Toolbox 1", category, "tools/toolbox.gif"); addIconRecord(ps, id++, "Toolbox 2", category, "tools/toolbox1.gif"); ps.close(); } // // AddIconRecord // Helper method to add an IconStore record. A PreparedStatement is // provided to which this method binds input parameters. Returns // true if the record was added. // protected static boolean addIconRecord( PreparedStatement ps, int id, String desc, int category, String filename) throws SQLException { // Create a file object for the icon File file = new File(filename); if (!file.exists()) { return false; } // Get the length of the file. This will be used when binding // the InputStream to the PreparedStatement. int len = (int) file.length(); FileInputStream inputStream; try { // Attempt to create an InputStream from the File object inputStream = new FileInputStream (filename); } catch (Exception ex) { // Some type of failure. Convert it into a SQLException. throw new SQLException (ex.getMessage ()); } // Set the parameters ps.setInt(1, id); ps.setString(2, desc); ps.setInt(3,category); ps.setBinaryStream(4, inputStream, len); // Now execute int rows = ps.executeUpdate(); return (rows == 0) ? false : true; } }
Previous | Table of Contents | Next |